USE [loan]
GO
/****** Object:  UserDefinedFunction [dbo].[CalculateInterest]    Script Date: 04/17/2013 14:46:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[CalInterestMnthly]
(
	@report_month INTEGER,
	@report_year INTEGER,
	@principal DECIMAL(18, 2),
	@interest_rate DECIMAL(18, 8),
	@loan_start_date DATETIME,
	--@loan_end_date DATETIME,
	@term INTEGER,	
	@payment_amount DECIMAL(18, 2)	
)
RETURNS DECIMAL(18, 2)
AS
BEGIN

DECLARE @interest DECIMAL(18, 8) = 0, 
	@mnthly_interest DECIMAL(18, 8) = 0,	
	@payment_date DATETIME = DATEADD(month, 1, @loan_start_date), 	
	@loan_end_date DATETIME = DATEADD(month, @term, @loan_start_date),
	@num_days INTEGER,
	@counter INTEGER = 1,
	@report_date DATETIME
	
SET @report_date = dbo.DateFromParts
(
	@report_year, 
	@report_month, 
	DAY(DATEADD(mm, DATEDIFF(mm, -1, dbo.DateFromParts(@report_year, @report_month, 1)), -1))
)

SET @interest_rate = (@interest_rate / 100) / 12

IF @loan_start_date < @report_date
BEGIN
	WHILE (YEAR(@payment_date) < @report_year OR (YEAR(@payment_date) = @report_year AND MONTH(@payment_date) < @report_month))
	BEGIN	
		IF @counter <= @term
		BEGIN
			SET @mnthly_interest = @principal * @interest_rate
			SET @interest = @interest + @mnthly_interest
			SET @principal = @principal + @mnthly_interest - @payment_amount
			SET @payment_date = DATEADD(month, 1, @payment_date)
			SET @counter = @counter + 1
		END	
		ELSE
		BEGIN
			BREAK
		END	
	END	
	
	SET @interest = 0
	
	WHILE @payment_date <= @report_date
	BEGIN
		IF @counter <= @term
		BEGIN
			SET @mnthly_interest = @principal * @interest_rate
			SET @interest = @interest + @mnthly_interest
			SET @principal = @principal + @mnthly_interest - @payment_amount
			SET @payment_date = DATEADD(month, 1, @payment_date)
			SET @counter = @counter + 1
		END	
		ELSE
		BEGIN
			BREAK
		END	
	END
END
RETURN @interest
END;


